﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using ModelGymTauro;
using System.IO;

namespace ControllerGymTauro
{
    public class ManagerGymTauro
    {
        
        public Boolean validoPassWord(int dni, String password)
        {

            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM USUARIO WHERE dni= " + dni;
            MySqlDataReader myReader = comm.ExecuteReader();
            String passwordFound = "";
            while (myReader.Read())
            {
                passwordFound = myReader["password"].ToString();


            }
            myReader.Close();
            conn.Close();

            return passwordFound.Equals(password);


        }

        public String createPassword(Persona e)
        {
            Char[] password = new Char[7];
            password[0] = (e.Nombre[0].ToString().ToLower())[0];
            password[1] = e.Nombre[1];
            password[2] = e.Nombre[2];
            password[3] = (e.Apellido[0].ToString().ToLower())[0];
            password[4] = e.Apellido[1];
            password[5] = e.Apellido[2];
            String s = new String(password);
            return s;

        }

        public String convertDateSql(String date)
        {
            Char[] create = new Char[11];
            create[0] = date[6];
            create[1] = date[7];
            create[2] = date[8];
            create[3] = date[9];
            create[4] = '-';
            create[5] = date[3];
            create[6] = date[4];
            create[7] = '-';
            create[8] = date[0];
            create[9] = date[1];
            create[10] = '0';
            return create.ToString();
        }

        /**********************************************************************************************/
        /************************************     USER    ****************************************/
        /**********************************************************************************************/


        public void addUser(Persona e)
        {

            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText =
                    "INSERT INTO USUARIO(dni, rol, password) " +
                    "VALUES (@Param1, @Param2, @Param3)";
            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.Int32);
            param1.Value = e.Dni;
            MySqlParameter param2 = new MySqlParameter("@Param2", MySqlDbType.VarChar, 20);
            if (e is Empleado) param2.Value = ((Empleado)e).TipoEmpleado;
            else param2.Value = "deportista";
            MySqlParameter param3 = new MySqlParameter("@Param3", MySqlDbType.VarChar, 100);
            String password = createPassword(e);
            param3.Value = password;

            comm.Parameters.Add(param1);
            comm.Parameters.Add(param2);
            comm.Parameters.Add(param3);

            comm.ExecuteNonQuery();
            conn.Close();
        }

        public String getTipoByUser(int dni)
        {
            String tipo = "";
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM USUARIO WHERE dni= " + dni;
            MySqlDataReader myReader = comm.ExecuteReader();

            while (myReader.Read())
            {
                tipo = myReader["rol"].ToString();
            }
            myReader.Close();
            conn.Close();

            return tipo;
        }

        public String getNameByUser(int dni)
        {
            String name = "";
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM EMPLEADO WHERE dni= " + dni;
            MySqlDataReader myReader = comm.ExecuteReader();

            while (myReader.Read())
            {
                name = myReader["nombre"].ToString();
            }
            myReader.Close();
            conn.Close();

            return name;
        }

        /**********************************************************************************************/
        /************************************     EMPLEADO    ****************************************/
        /**********************************************************************************************/


        public void addEmpleado(Empleado e)
        {
            e.FechaNacimiento = new DateTime(10, 10, 10);

            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText =
                    "INSERT INTO EMPLEADO(dni, nombre, apellido, fechaNacimiento, telefono, direccion, correo,tipoEmpleado,estado,fechaIngreso,sexo,foto) " +
                    "VALUES (@Param1, @Param2, @Param3, @Param4, @Param5, @Param6, @Param7, @Param8, @Param9, @Param10,@Param11,@Param12)";
            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.Int32);
            param1.Value = e.Dni;
            MySqlParameter param2 = new MySqlParameter("@Param2", MySqlDbType.VarChar, 20);
            param2.Value = e.Nombre;
            MySqlParameter param3 = new MySqlParameter("@Param3", MySqlDbType.VarChar, 100);
            param3.Value = e.Apellido;
            MySqlParameter param4 = new MySqlParameter("@Param4", MySqlDbType.Date);
            param4.Value = e.FechaNacimiento;
            MySqlParameter param5 = new MySqlParameter("@Param5", MySqlDbType.VarChar, 20);
            param5.Value = e.Telefono;
            MySqlParameter param6 = new MySqlParameter("@Param6", MySqlDbType.VarChar, 20);
            param6.Value = e.Direccion;
            MySqlParameter param7 = new MySqlParameter("@Param7", MySqlDbType.VarChar, 20);
            param7.Value = e.Correo;
            MySqlParameter param8 = new MySqlParameter("@Param8", MySqlDbType.VarChar, 20);
            param8.Value = e.TipoEmpleado;
            MySqlParameter param9 = new MySqlParameter("@Param9", MySqlDbType.VarChar, 20);
            param9.Value = e.Estado;
            MySqlParameter param10 = new MySqlParameter("@Param10", MySqlDbType.Date);
            param10.Value = e.FechaIngreso;
            MySqlParameter param11 = new MySqlParameter("@Param11", MySqlDbType.VarChar, 1);
            param11.Value = e.Sexo;
            MySqlParameter param12 = new MySqlParameter("@Param11", MySqlDbType.Blob);

            System.IO.FileStream fs = new FileStream(e.UrlFoto, System.IO.FileMode.Open, System.IO.FileAccess.Read);
            byte[] fileData = new byte[fs.Length];
            fs.Read(fileData, 0, System.Convert.ToInt32(fs.Length));
            fs.Close();
            param12.Value = fileData;
            comm.Parameters.Add(param1);
            comm.Parameters.Add(param2);
            comm.Parameters.Add(param3);
            comm.Parameters.Add(param4);
            comm.Parameters.Add(param5);
            comm.Parameters.Add(param6);
            comm.Parameters.Add(param7);
            comm.Parameters.Add(param8);
            comm.Parameters.Add(param9);
            comm.Parameters.Add(param10);
            comm.Parameters.Add(param11);
            comm.Parameters.Add(param12);
            comm.ExecuteNonQuery();
            conn.Close();
        }

        public Empleado getEmpleadoByDni(int dni)
        {
            String name = "";
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM EMPLEADO WHERE dni= " + dni;
            MySqlDataReader myReader = comm.ExecuteReader();
            Empleado e = new Empleado();
            while (myReader.Read())
            {
                e.Nombre = myReader["nombre"].ToString();
                e.Foto = (byte[])myReader["foto"];
                e.Id = Int32.Parse(myReader["idEmpleado"].ToString());
                e.Dni = myReader["dni"].ToString();
                e.Apellido=myReader["apellido"].ToString();
                e.FechaNacimiento = DateTime.Parse(myReader["fechaNacimiento"].ToString());
                e.Telefono= myReader["telefono"].ToString();
                e.Direccion= myReader["direccion"].ToString();
                e.Correo= myReader["correo"].ToString();
                e.TipoEmpleado = myReader["tipoEmpleado"].ToString();
                e.Estado = myReader["estado"].ToString();
                e.FechaIngreso = DateTime.Parse(myReader["fechaIngreso"].ToString());

            }
            myReader.Close();
            conn.Close();

            return e;

        }

        

        /**********************************************************************************************/
        /************************************     DEPORTISTA    ****************************************/
        /**********************************************************************************************/

        public Deportista queryDeportistaById(int id) {

            String name = "";
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM DEPORTISTA WHERE idDeportista= " + id;
            MySqlDataReader myReader = comm.ExecuteReader();
            Deportista e = new Deportista();
            while (myReader.Read())
            {
                e.Nombre = myReader["nombre"].ToString();
                e.Foto = (byte[])myReader["foto"];
                e.Id = Int32.Parse(myReader["idEmpleado"].ToString());
                e.Dni = myReader["dni"].ToString();
                e.Apellido = myReader["apellido"].ToString();
                e.FechaNacimiento = DateTime.Parse(myReader["fechaNacimiento"].ToString());
                e.Telefono = myReader["telefono"].ToString();
                e.Direccion = myReader["direccion"].ToString();
                e.Correo = myReader["correo"].ToString();
                e.Estado = myReader["estado"].ToString();
               

            }
            myReader.Close();
            conn.Close();

            return e;
        
        }
        
        public List<Deportista> queryAllDeportista()
        {
            List<Deportista> lista = new List<Deportista>();
            String name = "";
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM DEPORTISTA";
            MySqlDataReader myReader = comm.ExecuteReader();
            
            while (myReader.Read())
            {
                Deportista e = new Deportista();
                e.Nombre = myReader["nombre"].ToString();
                e.Foto = (byte[])myReader["foto"];
                e.Id = Int32.Parse(myReader["idDeportista"].ToString());
                e.Dni = myReader["dni"].ToString();
                e.Apellido = myReader["apellido"].ToString();
                e.FechaNacimiento = DateTime.Parse(myReader["fechaNacimiento"].ToString());
                e.Telefono = myReader["telefono"].ToString();
                e.Direccion = myReader["direccion"].ToString();
                e.Correo = myReader["correo"].ToString();
                e.Estado = myReader["estado"].ToString();
                lista.Add(e);

            }
            myReader.Close();
            conn.Close();

            return lista;
        }
      
        public void updateDeportistaById(Deportista d)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "UPDATE DEPORTISTA " +
                               "SET telefono = @Param1 AND direccion = @Param2 AND" +
                                   "correo = @Param3 AND estado = @Param4" +
                               "WHERE idDeportista= " + d.Id;

            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.VarChar, 100);
            param1.Value = d.Telefono;
            MySqlParameter param2 = new MySqlParameter("@Param2", MySqlDbType.VarChar, 100);
            param2.Value = d.Direccion;
            MySqlParameter param3 = new MySqlParameter("@Param3", MySqlDbType.VarChar, 100);
            param3.Value = d.Correo;
            MySqlParameter param4 = new MySqlParameter("@Param4", MySqlDbType.VarChar, 20);
            param4.Value = d.Estado;
            comm.Parameters.Add(param1);
            comm.Parameters.Add(param2);
            comm.Parameters.Add(param3);
            comm.Parameters.Add(param4);
            comm.ExecuteNonQuery();
            conn.Close();
        }


        public void addDeportista(Deportista e)
        {
            this.addUser(e);
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText =
                    "INSERT INTO DEPORTISTA(dni, nombre, apellido, fechaNacimiento, telefono, direccion, correo,fechaRegistro,estado,sexo,foto) " +
                    "VALUES (@Param1, @Param2, @Param3, @Param4, @Param5, @Param6, @Param7, @Param8, @Param9, @Param10, @Param11)";
            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.Int32);
            param1.Value = e.Dni;
            MySqlParameter param2 = new MySqlParameter("@Param2", MySqlDbType.VarChar, 20);
            param2.Value = e.Nombre;
            MySqlParameter param3 = new MySqlParameter("@Param3", MySqlDbType.VarChar, 100);
            param3.Value = e.Apellido;
            MySqlParameter param4 = new MySqlParameter("@Param4", MySqlDbType.Date);
            param4.Value = e.FechaNacimiento.ToString("yyyy-MM-dd");
            MySqlParameter param5 = new MySqlParameter("@Param5", MySqlDbType.VarChar, 20);
            param5.Value = e.Telefono;
            MySqlParameter param6 = new MySqlParameter("@Param6", MySqlDbType.VarChar, 20);
            param6.Value = e.Direccion;
            MySqlParameter param7 = new MySqlParameter("@Param7", MySqlDbType.VarChar, 20);
            param7.Value = e.Correo;
            MySqlParameter param8 = new MySqlParameter("@Param8", MySqlDbType.Date);
            param8.Value = e.FechaRegistro.ToString("yyyy-MM-dd");
            MySqlParameter param9 = new MySqlParameter("@Param9", MySqlDbType.VarChar, 20);
            param9.Value = e.Estado;
            MySqlParameter param10 = new MySqlParameter("@Param10", MySqlDbType.VarChar, 1);
            param10.Value = e.Sexo;
            MySqlParameter param11 = new MySqlParameter("@Param11", MySqlDbType.Blob);

            System.IO.FileStream fs = new FileStream(e.UrlFoto, System.IO.FileMode.Open, System.IO.FileAccess.Read);
            byte[] fileData = new byte[fs.Length];
            fs.Read(fileData, 0, System.Convert.ToInt32(fs.Length));
            fs.Close();

            param11.Value = fileData;

            comm.Parameters.Add(param1);
            comm.Parameters.Add(param2);
            comm.Parameters.Add(param3);
            comm.Parameters.Add(param4);
            comm.Parameters.Add(param5);
            comm.Parameters.Add(param6);
            comm.Parameters.Add(param7);
            comm.Parameters.Add(param8);
            comm.Parameters.Add(param9);
            comm.Parameters.Add(param10);
            comm.Parameters.Add(param11);

            comm.ExecuteNonQuery();
            conn.Close();
        }


        /**********************************************************************************************/
        /************************************     RUTINA    ****************************************/
        /**********************************************************************************************/

        public List<Rutina> queryAllRutina()
        {
            List<Rutina> lista = new List<Rutina>();
            String name = "";
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM RUTINA";
            MySqlDataReader myReader = comm.ExecuteReader();

            while (myReader.Read())
            {
                Rutina r = new Rutina();
                r.Nombre = myReader["nombre"].ToString();
                r.Descripcion = myReader["descripcion"].ToString();
                lista.Add(r);
            }
            myReader.Close();
            conn.Close();
            return lista;

        }

        public void addRutina(Rutina e)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText =
                    "INSERT INTO RUTINA(nombre, descripcion) " +
                    "VALUES (@Param1, @Param2)";
            MySqlParameter param1 = new MySqlParameter("@Param2", MySqlDbType.VarChar, 20);
            param1.Value = e.Nombre;
            MySqlParameter param2 = new MySqlParameter("@Param3", MySqlDbType.VarChar, 100);
            param2.Value = e.Descripcion;

            comm.Parameters.Add(param1);
            comm.Parameters.Add(param2);

            comm.ExecuteNonQuery();
            conn.Close();

        }


        /**********************************************************************************************/
        /************************************     ACTIVIDAD    ****************************************/
        /**********************************************************************************************/

        public void addActividad(Actividad a)
        {

            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText =
                    "INSERT INTO ACTIVIDAD(nombre, descripcion, duracion,estado) " +
                    "VALUES (@Param1, @Param2, @Param3, @Param4)";

            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.VarChar, 20);
            param1.Value = a.Nombre;
            MySqlParameter param2 = new MySqlParameter("@Param2", MySqlDbType.VarChar, 100);
            param2.Value = a.Descripcion;
            MySqlParameter param3 = new MySqlParameter("@Param3", MySqlDbType.Int32);
            param3.Value = a.Duracion;
            MySqlParameter param4 = new MySqlParameter("@Param4", MySqlDbType.VarChar, 100);
            param4.Value = a.Estado;

            comm.Parameters.Add(param1);
            comm.Parameters.Add(param2);
            comm.Parameters.Add(param3);
            comm.Parameters.Add(param4);

            comm.ExecuteNonQuery();
            conn.Close();
        }

        public void asignarActividadProfesor(String lugar, int idActividad, int idEmpleado)
        {

            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText =
                    "INSERT INTO CLASE(lugar, idEmpleado, idActividad) " +
                    "VALUES (@Param1, @Param2, @Param3)";
            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.VarChar, 50);
            param1.Value = lugar;
            MySqlParameter param2 = new MySqlParameter("@Param2", MySqlDbType.Int32);
            param2.Value = idEmpleado;
            MySqlParameter param3 = new MySqlParameter("@Param3", MySqlDbType.Int32);
            param3.Value = idActividad;

            comm.Parameters.Add(param1);
            comm.Parameters.Add(param2);
            comm.Parameters.Add(param3);

            comm.ExecuteNonQuery();
            conn.Close();
        }

        public void updateActividadById(Actividad d)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "UPDATE ACTIVIDAD " +
                               "SET descripcion = @Param1 AND nombre = @Param2 AND" +
                                   "duracion = @Param3 AND estado = @Param4" +
                               "WHERE idActividad= " + d.Id;

            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.VarChar, 100);
            param1.Value = d.Descripcion;
            MySqlParameter param2 = new MySqlParameter("@Param2", MySqlDbType.VarChar, 20);
            param2.Value = d.Nombre;
            MySqlParameter param3 = new MySqlParameter("@Param3", MySqlDbType.Int32);
            param3.Value = d.Duracion;
            MySqlParameter param4 = new MySqlParameter("@Param4", MySqlDbType.VarChar, 100);
            param4.Value = d.Estado;

            comm.Parameters.Add(param1);
            comm.Parameters.Add(param2);
            comm.Parameters.Add(param3);
            comm.Parameters.Add(param4);
            comm.ExecuteNonQuery();
            conn.Close();
        }

        public void addTipoMembresia(Membresia m) {

            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText =
                    "INSERT INTO MEMBRESIA(nombre, periodo, costo) " +
                    "VALUES (@Param1, @Param2, @Param3)";

            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.VarChar, 20);
            param1.Value = m.Nombre;
            MySqlParameter param2 = new MySqlParameter("@Param2", MySqlDbType.VarChar, 20);
            param2.Value = m.Periodo;
            MySqlParameter param3 = new MySqlParameter("@Param3", MySqlDbType.Float);
            param3.Value = m.Costo;            

            comm.Parameters.Add(param1);
            comm.Parameters.Add(param2);
            comm.Parameters.Add(param3);

            comm.ExecuteNonQuery();
            conn.Close();       
        }

        public List<Actividad> queryAllActividades() {
            List<Actividad> listaAct = new List<Actividad>();
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM ACTIVIDAD";
            MySqlDataReader myReader = comm.ExecuteReader();

            while (myReader.Read())
            {
                Actividad a = new Actividad();
                a.Id = Int32.Parse(myReader["idActividad"].ToString());
                a.Estado = myReader["estado"].ToString();
                a.Descripcion = myReader["descripcion"].ToString();
                a.Duracion = Int32.Parse(myReader["duracion"].ToString());
                a.Nombre = myReader["nombre"].ToString();
                listaAct.Add(a);

            }
            myReader.Close();
            conn.Close();
            return listaAct;
        }

        /**********************************************************************************************/
        /************************************     MEMBRESIA    ****************************************/
        /**********************************************************************************************/

        public Membresia getMembresiaById(int idMembresia)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM MEMBRESIA WHERE idMembresia = " + idMembresia;

            MySqlDataReader myReader = comm.ExecuteReader();
            Membresia membresia = new Membresia();

            if (myReader.Read())
            {
                membresia.Id = idMembresia;
                membresia.Nombre = myReader["descuento"].ToString();
                membresia.Periodo = myReader["fechaInicio"].ToString();
                membresia.Costo = Double.Parse(myReader["fechaFin"].ToString());
            }

            myReader.Close();
            conn.Close();
            return membresia;
        }

        public void updateMembresiaById(Membresia membresia)
        {
            MySqlConnection conn = new MySqlConnection(); //(nombre, periodo, costo
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "UPDATE MEMBRESIA " +
                               "SET nombre = @Param1 AND periodo = @Param2 AND costo = @Param3 " +
                               "WHERE idMembresia= " + membresia.Id;

            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.Float);
            param1.Value = membresia.Nombre;
            MySqlParameter param2 = new MySqlParameter("@Param2", MySqlDbType.Date);
            param2.Value = membresia.Periodo;
            MySqlParameter param3 = new MySqlParameter("@Param3", MySqlDbType.Date);
            param3.Value = membresia.Costo;

            comm.Parameters.Add(param1);
            comm.Parameters.Add(param2);
            comm.Parameters.Add(param3);

            comm.ExecuteNonQuery();
            conn.Close();
        }

        public List<Membresia> queryAllMembresia() {
            List<Membresia> listMemb = new List<Membresia>();
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM MEMBRESIA";
            MySqlDataReader myReader = comm.ExecuteReader();

            while (myReader.Read())
            {
                Membresia m = new Membresia();
                m.Id = Int32.Parse(myReader["idMembresia"].ToString());
                m.Nombre = myReader["nombre"].ToString();
                m.Periodo = myReader["periodo"].ToString();
                m.Costo = Double.Parse(myReader["costo"].ToString());

                listMemb.Add(m);

            }
            myReader.Close();
            conn.Close();
            return listMemb;        
        }

        /**********************************************************************************************/
        /************************************     PROMOCION    ****************************************/
        /**********************************************************************************************/

        public void addPromocion(Promocion prom, int idProd)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "INSERT INTO PROMOCION(idProducto, descuento, fechaInicio, fechaFin, descripcion, estado) " +
                               "VALUES (@Param0, @Param1, @Param2, @Param3, @Param4, @Param5)";

            MySqlParameter param0 = new MySqlParameter("@Param0", MySqlDbType.Int32);
            param0.Value = idProd; 
            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.Float);
            param1.Value = prom.Descuento;
            MySqlParameter param2 = new MySqlParameter("@Param2", MySqlDbType.Date);
            param2.Value = prom.FechaInicio;
            MySqlParameter param3 = new MySqlParameter("@Param3", MySqlDbType.Date);
            param3.Value = prom.FechaFin;
            MySqlParameter param4 = new MySqlParameter("@Param4", MySqlDbType.VarChar, 50);
            param4.Value = prom.Descripcion;
            MySqlParameter param5 = new MySqlParameter("@Param5", MySqlDbType.VarChar, 20);
            param5.Value = prom.Estado;

            comm.Parameters.Add(param0); 
            comm.Parameters.Add(param1);
            comm.Parameters.Add(param2);
            comm.Parameters.Add(param3);
            comm.Parameters.Add(param4);
            comm.Parameters.Add(param5);

            comm.ExecuteNonQuery();
            conn.Close();
        }

        public Promocion getPromocionById(int idProm)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM PROMOCION WHERE idPromocion= " + idProm;

            MySqlDataReader myReader = comm.ExecuteReader();
            Promocion prom = new Promocion();

            if (myReader.Read())
            {
                prom.Id = idProm;
                prom.Descuento = Double.Parse(myReader["descuento"].ToString());
                prom.FechaInicio = DateTime.Parse(myReader["fechaInicio"].ToString());
                prom.FechaFin = DateTime.Parse(myReader["fechaFin"].ToString());
                prom.Descripcion = myReader["descripcion"].ToString();
            }

            myReader.Close();
            conn.Close();
            return prom;
        }

        public void updatePromocionById(Promocion prom)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "UPDATE PROMOCION " +
                               "SET descuento = @Param1 AND fechaInicio = @Param2 AND" +
                                   "fechaFin = @Param3 AND  descripcion = @Param4 AND estado = @Param5" +
                               "WHERE idPromocion= " + prom.Id;

            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.Float);
            param1.Value = prom.Descuento;
            MySqlParameter param2 = new MySqlParameter("@Param2", MySqlDbType.Date);
            param2.Value = prom.FechaInicio;
            MySqlParameter param3 = new MySqlParameter("@Param3", MySqlDbType.Date);
            param3.Value = prom.FechaFin;
            MySqlParameter param4 = new MySqlParameter("@Param4", MySqlDbType.VarChar, 50);
            param4.Value = prom.Descripcion;
            MySqlParameter param5 = new MySqlParameter("@Param5", MySqlDbType.VarChar, 20);
            param5.Value = prom.Estado;

            comm.Parameters.Add(param1);
            comm.Parameters.Add(param2);
            comm.Parameters.Add(param3);
            comm.Parameters.Add(param4);
            comm.Parameters.Add(param5);

            comm.ExecuteNonQuery();
            conn.Close();
        }

        public List<Promocion> allPromocion()
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM PROMOCION";

            MySqlDataReader myReader = comm.ExecuteReader();
            List<Promocion> listaProm = new List<Promocion>();

            while (myReader.Read())
            {
                Promocion prom = new Promocion();

                prom.Id = Int32.Parse(myReader["idPromocion"].ToString());
                prom.Descuento = Double.Parse(myReader["descuento"].ToString());
                prom.FechaInicio = DateTime.Parse(myReader["fechaInicio"].ToString());
                prom.FechaFin = DateTime.Parse(myReader["fechaFin"].ToString());
                prom.Descripcion = myReader["descripcion"].ToString();
                prom.Estado = myReader["estado"].ToString();

                listaProm.Add(prom);
            }

            myReader.Close();
            conn.Close();
            return listaProm;
        }

        /**********************************************************************************************/
        /************************************     PRODUCTO    ****************************************/
        /**********************************************************************************************/

        public void addProducto(Producto prod)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "INSERT INTO PRODUCTO(nombre, marca, tipo, stock, precioVenta) " +
                               "VALUES (@Param1, @Param2, @Param3, @Param4, @Param5)";

            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.VarChar, 20);
            param1.Value = prod.Nombre;
            MySqlParameter param2 = new MySqlParameter("@Param2", MySqlDbType.VarChar, 20);
            param2.Value = prod.Marca;
            MySqlParameter param3 = new MySqlParameter("@Param3", MySqlDbType.VarChar, 20);
            param3.Value = prod.Tipo;
            MySqlParameter param4 = new MySqlParameter("@Param4", MySqlDbType.Int32);
            param4.Value = prod.Stock;
            MySqlParameter param5 = new MySqlParameter("@Param5", MySqlDbType.Float);
            param5.Value = prod.PrecioVenta;

            comm.Parameters.Add(param1);
            comm.Parameters.Add(param2);
            comm.Parameters.Add(param3);
            comm.Parameters.Add(param4);
            comm.Parameters.Add(param5);

            comm.ExecuteNonQuery();
            conn.Close();
        }

        public Producto getProductoById(int idProd)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM PRODUCTO WHERE idProducto= " + idProd;

            MySqlDataReader myReader = comm.ExecuteReader();
            Producto prod = null;

            if (myReader.Read())
            {
                prod = new Producto();
                prod.Id = idProd;
                prod.Nombre = myReader["nombre"].ToString();
                prod.Marca = myReader["marca"].ToString();
                prod.Tipo = myReader["tipo"].ToString();
                prod.Stock = Int32.Parse(myReader["stock"].ToString());
                prod.PrecioVenta = Double.Parse(myReader["precioVenta"].ToString());
            }

            myReader.Close();
            conn.Close();
            return prod;
        }

        public void updateProductoById(Producto prod)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "UPDATE PRODUCTO " +
                               "SET nombre = @Param1 AND marca = @Param2 AND" +
                                   "tipo = @Param3 AND  stock = @Param4 AND precioVenta = @Param5" +
                               "WHERE idProducto= " + prod.Id;

            MySqlParameter param1 = new MySqlParameter("@Param1", MySqlDbType.VarChar, 20);
            param1.Value = prod.Nombre;
            MySqlParameter param2 = new MySqlParameter("@Param2", MySqlDbType.VarChar, 20);
            param2.Value = prod.Marca;
            MySqlParameter param3 = new MySqlParameter("@Param3", MySqlDbType.VarChar, 20);
            param3.Value = prod.Tipo;
            MySqlParameter param4 = new MySqlParameter("@Param4", MySqlDbType.Int32);
            param4.Value = prod.Stock;
            MySqlParameter param5 = new MySqlParameter("@Param5", MySqlDbType.Float);
            param5.Value = prod.PrecioVenta;

            comm.Parameters.Add(param1);
            comm.Parameters.Add(param2);
            comm.Parameters.Add(param3);
            comm.Parameters.Add(param4);
            comm.Parameters.Add(param5);

            comm.ExecuteNonQuery();
            conn.Close();
        }

        public List<Producto> allProducto(string sentencia)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "Database=sql544036;" +
                    "Data Source=sql5.freesqldatabase.com;User Id=sql544036;Password=uR6*dQ4*";
            conn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM PRODUCTO" + sentencia;

            MySqlDataReader myReader = comm.ExecuteReader();
            List<Producto> listaProm = new List<Producto>();

            while (myReader.Read())
            {
                Producto prod = new Producto();

                prod.Id = Int32.Parse(myReader["idProducto"].ToString());
                prod.Nombre = myReader["nombre"].ToString();
                prod.Marca = myReader["marca"].ToString();
                prod.Tipo = myReader["tipo"].ToString();
                prod.Stock = Int32.Parse(myReader["stock"].ToString());
                prod.PrecioVenta = Double.Parse(myReader["precioVenta"].ToString());

                listaProm.Add(prod);
            }

            myReader.Close();
            conn.Close();
            return listaProm;
        }


        /**********************************************************************************************/
        /*************************************     DIETA    *******************************************/
        /**********************************************************************************************/




        /**********************************************************************************************/
        /********************************     DETALLE DE DIETA    *************************************/
        /**********************************************************************************************/
    }
}
